from future import __annotations__
from fastapi import FastAPI
from pyhive import hive
from datetime import datetime
from typing import NamedTuple
from collections import defaultdict
from copy import deepcopy
from fastapi.middleware.cors import CORSMiddleware
import pymysql
conn = hive.connect(host = '113.31.105.242',port = 10000, database = 'default')

db = pymysql.connect(host='113.31.110.212',
                     user='root',
                     password='zzh0117.',
                     database='advertise')
app = FastAPI()

origins = [ "*"  ]

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)



click = NamedTuple('click',[('ip',str),('time',str),('os',str),('province',str),('city',str),('advertise',str)])
expose = NamedTuple('expose',[('time',str),('ip',str),('os',str),('province',str),('city',str),('advertise',str)])
city_list = {
  "北京": ["北京"],
  "天津": ["天津"],
  "山西": ["太原", "阳泉", "晋城", "长治", "临汾", "运城", "忻州", "吕梁", "晋中", "大同", "朔州"],
  "河北": ["沧州", "石家庄", "唐山", "保定", "廊坊", "衡水", "邯郸", "邢台", "张家口", "辛集", "秦皇岛", "定州", "承德", "涿州"],
  "山东": ["济南", "淄博", "聊城", "德州", "滨州", "济宁", "菏泽", "枣庄", "烟台", "威海", "泰安", "青岛", "临沂", "莱芜", "东营", "潍坊", "日照"],
  "河南": ["郑州", "新乡", "鹤壁", "安阳", "焦作", "濮阳", "开封", "驻马店", "商丘", "三门峡", "南阳", "洛阳", "周口", "许昌", "信阳", "漯河", "平顶山", "济源"],
  "广东": ["珠海", "中山", "肇庆", "深圳", "清远", "揭阳", "江门", "惠州", "河源", "广州", "佛山", "东莞", "潮州", "汕尾", "梅州", "阳江", "云浮", "韶关", "湛江", "汕头", "茂名"],
  "浙江": ["舟山", "温州", "台州", "绍兴", "衢州", "宁波", "丽水", "金华", "嘉兴", "湖州", "杭州"],
  "宁夏": ["中卫", "银川", "吴忠", "石嘴山", "固原"],
  "江苏": ["镇江", "扬州", "盐城", "徐州", "宿迁", "无锡", "苏州", "南通", "南京", "连云港", "淮安", "常州", "泰州"],
  "湖南": ["长沙", "邵阳", "怀化", "株洲", "张家界", "永州", "益阳", "湘西", "娄底", "衡阳", "郴州", "岳阳", "常德", "湘潭"],
  "吉林": ["长春", "长春", "通化", "松原", "四平", "辽源", "吉林", "延边", "白山", "白城"],
  "福建": ["漳州", "厦门", "福州", "三明", "莆田", "宁德", "南平", "龙岩", "泉州"],
  "甘肃": ["张掖", "陇南", "兰州", "嘉峪关", "白银", "武威", "天水", "庆阳", "平凉", "临夏", "酒泉", "金昌", "甘南", "定西"],
  "陕西": ["榆林", "西安", "延安", "咸阳", "渭南", "铜川", "商洛", "汉中", "宝鸡", "安康"],
  "辽宁": ["营口", "铁岭", "沈阳", "盘锦", "辽阳", "锦州", "葫芦岛", "阜新", "抚顺", "丹东", "大连", "朝阳", "本溪", "鞍山"],
  "江西": ["鹰潭", "宜春", "上饶", "萍乡", "南昌", "景德镇", "吉安", "抚州", "新余", "九江", "赣州"],
  "黑龙江": ["伊春", "七台河", "牡丹江", "鸡西", "黑河", "鹤岗", "哈尔滨", "大兴安岭", "绥化", "双鸭山", "齐齐哈尔", "佳木斯", "大庆"],
  "安徽": ["宣城", "铜陵", "六安", "黄山", "淮南", "合肥", "阜阳", "亳州", "安庆", "池州", "宿州", "芜湖", "马鞍山", "淮北", "滁州", "蚌埠"],
  "湖北": ["孝感", "武汉", "十堰", "荆门", "黄冈", "襄阳", "咸宁", "随州", "黄石", "恩施", "鄂州", "荆州", "宜昌", "潜江", "天门", "神农架", "仙桃"],
  "青海": ["西宁", "海西", "海东", "玉树", "黄南", "海南", "海北", "果洛"],
  "新疆": ["乌鲁木齐", "克州", "阿勒泰", "五家渠", "石河子", "伊犁", "吐鲁番", "塔城", "克拉玛依", "喀什", "和田", "哈密", "昌吉", "博尔塔拉", "阿克苏", "巴音郭楞", "阿拉尔", "图木舒克", "铁门关"],
  "贵州": ["铜仁", "黔东南", "贵阳", "安顺", "遵义", "黔西南", "黔南", "六盘水", "毕节"],
  "四川": ["遂宁", "攀枝花", "眉山", "凉山", "成都", "巴中", "广安", "自贡", "甘孜", "资阳", "宜宾", "雅安", "内江", "南充", "绵阳", "泸州", "凉山", "乐山", "广元", "甘孜", "德阳", "达州", "阿坝"],
  "上海": ["上海"],
  "广西": ["南宁", "贵港", "玉林", "梧州", "钦州", "柳州", "来宾", "贺州", "河池", "桂林", "防城港", "崇左", "北海", "百色"],
  "西藏": ["拉萨", "山南", "日喀则", "那曲", "林芝", "昌都", "阿里"],
  "云南": ["昆明", "红河", "大理", "玉溪", "昭通", "西双版纳", "文山", "曲靖", "普洱", "怒江", "临沧", "丽江", "红河", "迪庆", "德宏", "大理", "楚雄", "保山"],
  "内蒙古": ["呼和浩特", "乌兰察布", "兴安", "赤峰", "呼伦贝尔", "锡林郭勒", "乌海", "通辽", "巴彦淖尔", "阿拉善", "鄂尔多斯", "包头"],
  "海南": ["海口", "三沙", "三亚", "临高", "五指山", "陵水", "文昌", "万宁", "白沙", "乐东", "澄迈", "屯昌", "定安", "东方", "保亭", "琼中", "琼海", "儋州", "昌江"],
  "重庆": ["重庆"]
}





def get_province(province):
    if province is None:
        return None
    for key in city_list.keys():
        if key in province:
            return key

def get_city(province,city):
    if province is None:
        return None
    for item in city_list[province]:
        if item in city:
            return item




@app.get("/os_view/{start_time}/{end_time}")
def os_view(start_time:str|None,end_time:str|None):
    os_count = defaultdict(int)
    print(start_time,end_time)
    cursor = conn.cursor()
    if 'null' in start_time or 'null' in end_time:
        suffix = ""
    else:
        suffix = f"where click_time between '{start_time}' and '{end_time}'"
    print(suffix)
    cursor.execute('select * from click_table ' + suffix)
    click_result = cursor.fetchall()
    clicks = []
    for item in click_result:
        item = item[1:]
        clicks.append(click(ip=item[0],province=item[1],city=item[2],os=item[3],time=item[4],advertise=item[5]))
    cursor.execute('select * from expose_table ' + suffix)
    expose_result = cursor.fetchall()
    exposes = []
    for item in expose_result:
        item = item[1:]
        exposes.append(expose(ip=item[0],province=item[1],city=item[2],os=item[3],time=item[4],advertise=item[5]))
    cursor.close()
    for cli in clicks:
        if 'window' in cli.os.lower():
            os_count['window'] += 1
        elif 'android' in cli.os.lower():
            os_count['android'] += 1
        elif 'linux' in cli.os.lower():
            os_count['linux'] += 1
        elif 'mac' in cli.os.lower():
            os_count['mac'] += 1
        else:
            os_count['other'] += 1
    click_out = []
    for k,v in os_count.items():
        click_out.append({"name":k,"value":v})
    os_count = defaultdict(int)
    for exp in exposes:
        if 'window' in exp.os.lower():
            os_count['window'] += 1
        elif 'android' in exp.os.lower():
            os_count['android'] += 1
        elif 'linux' in exp.os.lower():
            os_count['linux'] += 1
        elif 'mac' in exp.os.lower():
            os_count['mac'] += 1
        else:
            os_count['other'] += 1
    expose_out = []
    for k,v in os_count.items():
        expose_out.append({"name":k,"value":v})



    return {"code":200,"data":{"click":click_out,"expose":expose_out}}





@app.get("/province_view/{start_time}/{end_time}")
def province_view(start_time:str|None,end_time:str|None):
    citys_count = {}
    for province,citys in city_list.items():
        citys_count[province] =  {}
        for city in citys:
            citys_count[province][city] = 0
    expose_counts = deepcopy(citys_count)
    if 'null' in start_time or 'null' in end_time:
        suffix = ""
    else:
        suffix = f"where click_time between '{start_time}' and '{end_time}'"
    cursor = conn.cursor()
    cursor.execute('select * from click_table '+suffix)
    click_result = cursor.fetchall()
    clicks = []
    for item in click_result:
        print(item)
        item = item[1:]
        # clicks.append(click(ip=item[0],time=item[1],os=item[2],province=item[3],city=item[4],advertise=item[5]))
        clicks.append(click(ip=item[0],province=item[1],city=item[2],os=item[3],time=item[4],advertise=item[5]))
    cursor.execute('select * from expose_table')
    expose_result = cursor.fetchall()
    exposes = []
    for item in expose_result:
        item = item[1:]
        exposes.append(expose(ip=item[0],province=item[1],city=item[2],os=item[3],time=item[4],advertise=item[5]))
    cursor.close()

    for cli in clicks:
        province = get_province(cli.province)
        city = get_city(province,cli.city)
        if province is None or city is None:
            continue
        citys_count[province][city] += 1
    for exp in exposes:
        province = get_province(exp.province)
        city = get_city(province,exp.city)
        if province is None or city is None:
            continue
        expose_counts[province][city] += 1
    return {"code":200,"data":{"click":citys_count,"expose":expose_counts}}



@app.get("/backup_click")
def backup_click():

    sql = "select id, ip,province,city,os,click_time,advertise from click"
    hive_sql = "select * from click_table"
    hive_conn = conn.cursor()
    hive_conn.execute(hive_sql)
    hive_result = hive_conn.fetchall()
    ids = [ item[0] for item in hive_result if item[0] is not None]
    print(ids)
    last_id = max(ids)
    print(last_id)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    result = cursor.fetchall()
    for res in result:
        if res[0] > last_id:
            sql = f"insert into click_table(id,ip,province,city,os,click_time,advertise) values('{res[0]}','{res[1]}','{res[2]}','{res[3]}','{res[4]}','{res[5]}','{res[6]}')"
            hive_conn.execute(sql)
    hive_conn.close()
    sql = "select id, ip,province,city,os,expose_time,advertise from expose"
    hive_sql = "select * from expose_table"
    hive_conn = conn.cursor()
    hive_conn.execute(hive_sql)
    hive_result = hive_conn.fetchall()
    ids = [ item[0] for item in hive_result if item[0] is not None]
    if len(ids) == 0:
        ids = [0]
    print(ids)
    last_id = max(ids)
    print(last_id)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    result = cursor.fetchall()
    for res in result:
        if res[0] > last_id:
            sql = f"insert into expose_table(id,ip,province,city,os,click_time,advertise) values('{res[0]}','{res[1]}','{res[2]}','{res[3]}','{res[4]}','{res[5]}','{res[6]}')"
            hive_conn.execute(sql)
    hive_conn.close()
    return "ok"



def backup_expose():

    sql = "select id, ip,province,city,os,advertise from expose"
    hive_sql = "select * from expose_table"
    hive_conn = conn.cursor()
    hive_conn.execute(hive_sql)
    hive_result = hive_conn.fetchall()
    ids = [ item[0] for item in hive_result if item[0] is not None]
    print(ids)
    last_id = max(ids)
    print(last_id)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    result = cursor.fetchall()
    for res in result:
        if res[0] > last_id:
            sql = f"insert into expose_table(id,ip,province,city,os,click_time,advertise) values('{res[0]}','{res[1]}','{res[2]}','{res[3]}','{res[4]}','{res[5]}','{res[6]}')"
            hive_conn.execute(sql)
    hive_conn.close()
    return "ok"



if __name__=="__main__":
    import uvicorn
    uvicorn.run("api:app",host="0.0.0.0",port=5566,reload=True,workers=1)